package com.correo.database.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import com.correo.database.beans.CasillaCorreoBean;
import com.correo.database.beans.IBean;
import com.correo.database.beans.OfficeBean;
import com.correo.database.beans.UserBean;

public class ManagerDataBaseCasillaCorreo implements IManagerDataBase {

	private Connection conexion;
	public ManagerDataBaseCasillaCorreo(Connection connection) {
		conexion = connection;
	}

	/*
	 * @casillaCorreoId char(10),
    @createDate datetime,
    @description char(512),
    @password char(255),
    @updateDate datetime,
    @userId char(10)
	 * (non-Javadoc)
	 * @see com.correo.database.server.IManagerDataBase#alta(com.correo.database.beans.IBean)
	 */
	@Override
	public void alta(IBean dataBean) throws SQLException {
		
		String strQuery="{CALL usp_casillaCorreoInsert(?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((CasillaCorreoBean)dataBean).getId());
		callStm.setDate( 2,new Date(Calendar.getInstance().getTimeInMillis()));
		callStm.setString( 3,null);
		callStm.setString( 4,"passwordsacarlo");
		callStm.setString( 5,null);
		if(((CasillaCorreoBean)dataBean).getUser()==null){
			callStm.setString( 6,null);
		}else{
			callStm.setString( 6,((CasillaCorreoBean)dataBean).getUser().getId());
		}
		
		callStm.execute();
		getConnection().commit();
		callStm.close();

		//cargo las oficina asociada a la casilla
		if(((CasillaCorreoBean)dataBean).getListaOffice()!=null){
			Iterator<OfficeBean> it=((CasillaCorreoBean)dataBean).getListaOffice().iterator();
			OfficeBean oficina;
			while(it.hasNext()){
				strQuery="{CALL usp_correoOfficeInsert(?,?)}";
				callStm=getConnection().prepareCall(strQuery);
				oficina = it.next();
				callStm.setString( 1,((CasillaCorreoBean)dataBean).getId());
				callStm.setString( 2,oficina.getName());
				callStm.execute();
				getConnection().commit();
				callStm.close();
			}
		}
	}

	@Override
	public Connection getConnection() {		
		return conexion;
	}

	@Override
	public void modify(IBean dataBean) throws SQLException {
		String strQuery="{CALL usp_casillaCorreoUpdate(?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((CasillaCorreoBean)dataBean).getId());
		callStm.setDate( 2,new Date(Calendar.getInstance().getTimeInMillis()));
		callStm.setString( 3,null);
		callStm.setString( 4,"passwordsacarlo");
		callStm.setString( 5,null);
		if(((CasillaCorreoBean)dataBean).getUser()==null){
			callStm.setString( 6,null);
		}else{
			callStm.setString( 6,((CasillaCorreoBean)dataBean).getUser().getId());
		}
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		
		strQuery = "{CALL usp_correoOfficeDelete(?,?)}";
		callStm = getConnection().prepareCall(strQuery);
		callStm.setString(1, ((CasillaCorreoBean)dataBean).getId());
		callStm.setString(2,null);				
		callStm.execute();
		getConnection().commit();
		callStm.close();

		//cargo las oficina asociada a la casilla
		if(((CasillaCorreoBean)dataBean).getListaOffice()!=null){
			Iterator<OfficeBean> it=((CasillaCorreoBean)dataBean).getListaOffice().iterator();
			OfficeBean oficina;
			while(it.hasNext()){
				strQuery="{CALL usp_correoOfficeInsert(?,?)}";
				callStm=getConnection().prepareCall(strQuery);
				oficina = it.next();
				callStm.setString( 1,((CasillaCorreoBean)dataBean).getId());
				callStm.setString( 2,oficina.getName());
				callStm.execute();
				getConnection().commit();
				callStm.close();
			}
		}
	}

	/*
	 * [usp_casillaCorreoSelect]
	 * (non-Javadoc)
	 * @see com.correo.database.server.IManagerDataBase#refresh()
	 */
	@Override
	public List<IBean> refresh() throws SQLException, ClassNotFoundException {
		List<IBean> listaCasillaCorreo = new LinkedList<IBean>();
		String strQuery="{CALL usp_casillaCorreoSelect(?)}";
		CallableStatement stm = getConnection().prepareCall(strQuery);
		stm.setString(1, null);
		ResultSet result = stm.executeQuery();
		CasillaCorreoBean casilla=null;
		while(result.next()){
			casilla = new CasillaCorreoBean();
			casilla.setId(result.getString("casillaCorreoId"));
			UserBean user = new UserBean();
			user.setId(result.getString("userId"));
			casilla.setUser(user);
		
			listaCasillaCorreo.add(casilla);
		}
		stm.close();
		
		Iterator<IBean> it = listaCasillaCorreo.iterator();
		Set<OfficeBean> listaOficinas=null;
		OfficeBean oficina;
		while(it.hasNext()){
			listaOficinas=new LinkedHashSet<OfficeBean>();
			casilla = (CasillaCorreoBean) it.next();
			strQuery="{CALL usp_correoOfficeSelect(?,?)}";
			stm = getConnection().prepareCall(strQuery);
			stm.setString(1, casilla.getId());
			stm.setString(2, null);
			result = stm.executeQuery();
			while(result.next()){
				oficina = new OfficeBean();
				oficina.setName(result.getString("officeId"));
				listaOficinas.add(oficina);
			}
			casilla.setListaOffice(listaOficinas);
			stm.close();
			Iterator<OfficeBean> itera = casilla.getListaOffice().iterator();
			while(itera.hasNext()){
				ManagerDataBase.getInstance().getMngOfficeDataBase().getSelection(itera.next());
			}
			
		}
		return listaCasillaCorreo;
	}

	@Override
	public void remove(List<IBean> listBean) throws SQLException {
		if(listBean!=null){
			Iterator<IBean> it=listBean.iterator();
			CasillaCorreoBean casilla;
			String strQuery;
			CallableStatement stm=null;
			while(it.hasNext()){
				casilla = (CasillaCorreoBean) it.next();
				strQuery = "{CALL usp_correoOfficeDelete(?,?)}";
				stm = getConnection().prepareCall(strQuery);
				stm.setString(1, casilla.getId());
				stm.setString(2, null);
				stm.executeUpdate();
				stm.close();

				strQuery = "{CALL usp_casillaCorreoDelete(?)}";
				stm = getConnection().prepareCall(strQuery);
				stm.setString(1, casilla.getId());				
				stm.executeUpdate();
				stm.close();
			}
		}

	}

}
